package itsm.isperp.module.service.configuration;

import itsm.isperp.framework.collection.tree.ITreeNode;
import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.domain.DataRequest;
import itsm.isperp.framework.data.domain.DataResponse;
import itsm.isperp.framework.data.domain.FilterRule;
import itsm.isperp.framework.data.mybatis.domain.AbstractTreeNode;
import itsm.isperp.framework.domain.IUser;
import itsm.isperp.framework.service.BaseService;
import itsm.isperp.framework.utils.BeanUtils;
import itsm.isperp.framework.utils.JsonBuilder;
import itsm.isperp.framework.web.request.JqGridFilter;
import itsm.isperp.framework.web.request.JqGridFilterRule;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.module.dto.configuration.ConfCiDTO;
import itsm.isperp.module.entity.configuration.ConfCategoryProperty;
import itsm.isperp.module.entity.configuration.ConfCi;
import itsm.isperp.module.entity.configuration.ConfCiProperty;
import itsm.isperp.module.entity.configuration.ConfCiPropertyDatalog;
import itsm.isperp.module.entity.db.DbEntityDatalog;
import itsm.isperp.module.entity.incident.IcdIncident;
import itsm.isperp.module.repository.configuration.ConfCiMapper;
import itsm.isperp.module.service.app.AppUserService;
import itsm.isperp.module.service.change.ChgChangeService;
import itsm.isperp.module.service.db.DbEntityDatalogService;
import itsm.isperp.module.service.incident.IcdIncidentService;
import itsm.isperp.module.utils.CiProprtyUtils;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * 配置项服务
 * 
 * @author lizx
 * @date 2013-4-29
 */
@Service
public class ConfCiService extends BaseService<ConfCi> {

	public static final String CI_STATUS_USE = "在用";

	@Autowired
	ConfCiMapper confCiMapper;

	@Autowired
	ConfCiPropertyService confCiPropertyService;

	@Autowired
	ConfCategoryPropertyService confCategoryPropertyService;

	@Autowired
	ConfCiRelationService confCiRelationService;

	@Autowired
	ConfCiRelationTypeService confCiRelationTypeService;

	@Autowired
	ConfCiRelationUsePropetyService confCiRelationUsePropetyService;

	@Autowired
	IcdIncidentService icdIncidentService;

	@Autowired
	ChgChangeService chgChangeService;

	@Autowired
	DbEntityDatalogService dbEntityDatalogService;

	@Autowired
	ConfCiPropertyDatalogService confCiPropertyDatalogService;

	@Autowired
	AppUserService appUserService;

	@Autowired
	JdbcTemplate jdbcTemplate;

	@Override
	public ConfCiMapper getRepository() {
		return confCiMapper;
	}

	/**
	 * 根据关键字查询资产，关键字搜索范围为资产编号、使用者、维护者、管理者、客户
	 * 
	 * @param keyword
	 *            关键字
	 * @param customerId
	 *            客户ID
	 * @return
	 */
	public Page<ConfCi> searchByKeyword(String keyword, String customerId, Pageable pageable) {

		DataRequest req = new JqGridRequest(pageable);
		req.addFilterRule("code", JqGridFilterRuleOp.cn, keyword); // 编号
		req.addFilterRule("name", JqGridFilterRuleOp.cn, keyword); // 名称
		req.addFilterRule("confCategoryName", JqGridFilterRuleOp.cn, keyword); // 类别
		req.addFilterRule("useStaffName", JqGridFilterRuleOp.cn, keyword); // 使用者
		req.addFilterRule("managementStaffName", JqGridFilterRuleOp.cn, keyword); // 维护者
		req.addFilterRule("supportStaffName", JqGridFilterRuleOp.cn, keyword); // 管理者
		req.addFilterRule("cusCustomerId", JqGridFilterRuleOp.eq, customerId); // 客户
		return confCiMapper.findAllByRequest(req);
	}

	/**
	 * 批量更新资产
	 * 
	 * @param changeId
	 * @param entity
	 * @param properties
	 * @param req
	 * @param ps
	 * @param filter
	 * @return
	 */
	public int batchUpdate(String changeId, ConfCi entity, List<ConfCiProperty> properties, HttpServletRequest req, List<Map<String, String>> ps, JqGridFilter filter) {

		Set<String> includes = new HashSet<String>();

		Map<String, String[]> map = req.getParameterMap();

		for (String key : map.keySet()) {
			String[] value = map.get(key);
			if (value != null && value.length > 0 && StringUtils.isNotEmpty(value[0])) {
				includes.add(key);
			}
		}

		List<ConfCi> cis = this.listCiByFilter(ps, filter, "id");

		// Set<String> set = BeanUtils.getNotEmptyProperties(entity);

		for (ConfCi ci : cis) {
			ConfCi c = new ConfCi();
			BeanUtils.copyNotEmptyProperties(entity, c);
			c.setId(ci.getId());

			this.saveOrUpdate(changeId, c, properties, includes, 0);
		}
		return cis.size();
	}

	public int saveOrUpdate(String changeId, ConfCi entity, List<ConfCiProperty> properties, HttpServletRequest req) {

		Set<String> set = new HashSet<String>();
		set.addAll(req.getParameterMap().keySet());
		return saveOrUpdate(changeId, entity, properties, set, 1);
	}

	/**
	 * 更新ci实体以及属性，属性的更新方法为批量删除然后插入 para1 entity para2 properties 自定义属性列表
	 * 
	 * @return 保存的条数
	 */
	public int saveOrUpdate(String changeId, ConfCi entity, List<ConfCiProperty> properties, Set<String> includeColumns, int type) {

		String oldId = entity.getId();

		if (type == 1) {
			if (StringUtils.isEmpty(entity.getCode())) {
				return 0;
			}

			if (StringUtils.isNotEmpty(entity.getCode()) && !entity.getCode().equals(entity.getId())) {

				Integer countCode = jdbcTemplate.queryForObject("SELECT count(*) FROM conf_ci WHERE code=? AND ID<>'" + oldId + "'", Integer.class, entity.getCode());

				if (countCode > 0) {
					return 0;
				}
			}
		}

		IUser user = ContextHolder.getLoginUser();

		entity.setFinalChangedDate(new Timestamp(new Date().getTime()));
		entity.setFinalChanger(user.getFullname());

		boolean b = this.exists(oldId);
		if (b) {
			this.saveOrUpdateSpecific(entity, includeColumns);
		} else {
			//需要删除在表conf_ci_property冗余的数据，避免出错
			String sql="DELETE FROM conf_ci_property WHERE conf_ci_id='"+entity.getCode()+"'";
			jdbcTemplate.execute(sql);
			entity.setId(entity.getCode());
			this.saveOrUpdateSpecific(entity, includeColumns);
			this.confCiRelationService.updateRelationId(oldId, entity.getId());
		}

		if (StringUtils.isNotEmpty(changeId)) {
			jdbcTemplate.update("UPDATE chg_change SET ci_id='" + entity.getCode() + "' WHERE id='" + changeId + "' AND (ci_id IS NOT OR ci_id='')");

		}

		String id = entity.getId();
		// 获取最新的datalog(if)
		DbEntityDatalog last = dbEntityDatalogService.findLastByBusinessId(id);

		String datalogId = null;

		if (last != null && StringUtils.isEmpty(last.getChangeId()) && user.getUsername().equals(last.getCreator())) {
			Date d = new Date();
			long between = (d.getTime() - last.getCreateDate().getTime()) / 1000;

			// 5s
			if (between < 5) {
				datalogId = last.getId();
			}
		}

		if (datalogId != null) {
			if (changeId != null) {
				dbEntityDatalogService.updateChangeId(changeId, last.getId());
			}
			// 属性变更日志
			this.savePropertyChageLog(datalogId, changeId, entity, properties);
		} else {
			this.savePropertyChageLog(null, changeId, entity, properties);
		}

		// 更新属性信息
		this.saveOrUpdateCiProperty(changeId, entity, properties, type);
		return 1;
	}

	/**
	 * 批量保存配置项自定义属性 para1 ciid 配置项ID para2 properties 自定义属性列表
	 * 
	 * @return
	 */
	private void saveOrUpdateCiProperty(String changeId, ConfCi entity, List<ConfCiProperty> newP, int type) {
		if (type == 1) {
			confCiPropertyService.deleteByCiId(entity.getId());

		} else {
			if (newP.size() == 0) {
				return;
			}
			confCiPropertyService.deleteByCiIdAndProperties(entity.getId(), newP);
		}
		// 记录变更日志
		for (ConfCiProperty cpi : newP) {
			cpi.setConfCiId(entity.getId());
			confCiPropertyService.saveOrUpdate(cpi);
		}

		this.confCiRelationService.autoUpdateCiProperty(changeId, entity.getId());
	}

	// log
	public void savePropertyChageLog(String datalogId, String changeId, ConfCiProperty oldProperty, ConfCiProperty newProperty) {
		// old value and new value

	}

	// 属性变更日志
	private void savePropertyChageLog(String datalogId, String changeId, ConfCi entity, List<ConfCiProperty> newP) {

		List<ConfCategoryProperty> cateProperties = this.confCategoryPropertyService.findAllPropertyEx(entity.getConfCategoryId());
		List<ConfCiProperty> oldP = this.confCiPropertyService.findByCiId(entity.getId());

		if (cateProperties == null || cateProperties.size() == 0) {
			return;
		}

		Map<String, ConfCategoryProperty> cateMap = new HashMap<>();

		for (ConfCategoryProperty cp : cateProperties) {
			cateMap.put(cp.getId(), cp);
		}

		String sb = null;

		if ((oldP == null || oldP.size() == 0) && (newP != null && newP.size() > 0)) {
			// 没有历史的，只有新
			sb = CiProprtyUtils.getPropertyDescription(newP, cateMap, ":");

		} else if ((newP == null || newP.size() == 0) && (oldP != null && oldP.size() > 0)) {
			// 没有新的，只有历史的
			Map<String, ConfCiProperty> oldPMap = new HashMap<String, ConfCiProperty>();
			for (ConfCiProperty cp : oldP) {
				oldPMap.put(cp.getConfCategoryPropertyId(), cp);
			}
			sb = CiProprtyUtils.getPropertyDescription(oldP, cateMap, ":");

		} else if (oldP != null && newP != null) {
			Map<String, ConfCiProperty> newPMap = new HashMap<String, ConfCiProperty>();
			for (ConfCiProperty cp : newP) {
				newPMap.put(cp.getConfCategoryPropertyId(), cp);
			}
			Map<String, ConfCiProperty> oldPMap = new HashMap<String, ConfCiProperty>();
			for (ConfCiProperty cp : oldP) {
				oldPMap.put(cp.getConfCategoryPropertyId(), cp);
			}
			sb = CiProprtyUtils.getPropertyDescription(newPMap, oldPMap, cateMap);
		}
		if (StringUtils.isNotEmpty(sb) || datalogId != null) {
			ConfCiPropertyDatalog log = new ConfCiPropertyDatalog();

			log.setBusinessId(entity.getId());
			log.setChangeId(changeId);
			log.setDatalogId(datalogId);
			log.setDescription(sb);

			confCiPropertyDatalogService.saveOrUpdate(log);
		}
	}

	/**
	 * 删除实体及其自定义属性
	 */
	@Override
	public ResultMessage delete(String id) {
		super.delete(id);
		confCiPropertyService.deleteByCiId(id);

		return ResultMessage.success();
	}

	/**
	 * 获取获取故障履历,按时间降序排序
	 * 
	 * @param ciId
	 * @return
	 */
	public List<IcdIncident> findCiIncident(String ciId) {
		return icdIncidentService.findCiIncident(ciId);
	}

	/**
	 * 获取获取故障履历,按时间降序排序
	 * 
	 * @param ciId
	 * @return
	 */
	public List<Map<String, Object>> findCiIncidentDetail(String ciId) {
		return icdIncidentService.findCiIncidentDetail(ciId);
	}

	/**
	 * 获取变更履历
	 * 
	 * @param ciId
	 * @return
	 */
	public List<Map<String, Object>> findCiChange(String ciId) {
		return chgChangeService.findCiChangeDetail(ciId);
	}

	/**
	 * 获取资产的自定义属性值
	 * 
	 * @param ciId
	 * @return List
	 */
	public List<ConfCiProperty> getCiProperty(String ciId) {
		JqGridRequest req = new JqGridRequest();
		req.addFilterRule("confCiId", JqGridFilterRuleOp.eq, ciId); // 配置项ID
		return confCiPropertyService.findAll(req).getContent();
	}

	public DataResponse<ConfCi> findCustomerCiByCusUserId(String cusUserId, JqGridRequest req) {
		if ("null".equals(cusUserId) || StringUtils.isEmpty(cusUserId)) {
			return null;
		}

		AppUserService appUserService = ContextHolder.getSpringBean("appUserService");
		List<Map<String, Object>> projects = appUserService.findUserProjects(cusUserId);

		String condition = "'" + projects.get(0).get("cus_customer_id").toString() + "'";
		for (int i = 1; i < projects.size(); i++) {
			Map<String, Object> m = projects.get(i);
			String cus_customer_id = (String) m.get("cus_customer_id");
			condition += ",'" + cus_customer_id + "'";

		}

		req.addFilterRule("cus_customer_id", JqGridFilterRuleOp.in, condition);

		return this.confCiMapper.findAllByRequest(req);
	}

	/**
	 * 获取人员资产
	 * 
	 * @param cusUserId
	 *            客户的用户
	 * @param req
	 *            数据请求
	 * @return 资产分页数据
	 */
	public DataResponse<ConfCi> findByCusUserId(String cusUserId, JqGridRequest req) {
		if ("null".equals(cusUserId) || StringUtils.isEmpty(cusUserId)) {
			return null;
		}
		req.setSearchString(String.format("use_staff_id='%s' OR management_staff_id='%s' OR support_staff_id='%s'", cusUserId, cusUserId, cusUserId));

		return this.confCiMapper.findAllByRequest(req);
	}

	/**
	 * 更具
	 * 
	 * @param cusUserId
	 * @param req
	 * @return
	 */
	public DataResponse<Map<String, Object>> findDetailByCusUserId(String cusUserId, JqGridRequest req) {
		if ("null".equals(cusUserId) || StringUtils.isEmpty(cusUserId)) {
			return null;
		}
		req.setSearchString(String.format("use_staff_id='%s' OR management_staff_id='%s' OR support_staff_id='%s'", cusUserId, cusUserId, cusUserId));

		return this.confCiMapper.findAllDetailByRequest(req);
	}

	/**
	 * 
	 * @param groupId
	 * @param groupName
	 * @param rules
	 * @return
	 */
	public List<Map<String, Object>> getCiGroupList(String groupId, String groupName, JqGridFilterRule... rules) {

		if (rules == null) {
			return null;
		}
		JqGridRequest request = new JqGridRequest();
		for (FilterRule rule : rules)
			request.addFilterRule(rule);

		request.isPageable(false);
		request.setSelectColumns(groupId, groupName, "COUNT(1) AS count_id");

		request.setGroupBy(groupId + "," + groupName + ",order_num");
		request.setSort("order_num", Direction.ASC);
		request.addFilterRule("conf_category_name", JqGridFilterRuleOp.isnotnull, null);
		return this.getRepository().findAllDetailByRequest(request);

	}

	/**
	 * 
	 * @param groupId
	 * @param groupName
	 * @param rules
	 * @return
	 */
	public List<ITreeNode> getCiMenu(String groupId, String groupName, JqGridFilterRule... rules) {
		List<ITreeNode> nodes = new ArrayList<>();

		List<Map<String, Object>> list = getCiGroupList(groupId, groupName, rules);

		ITreeNode n = new AbstractTreeNode();
		n.setName("所有");
		n.setId("");
		n.setTreenodePath("/");
		n.setLeaf(true);
		nodes.add(n);

		for (Map<String, Object> map : list) {

			ITreeNode tn = new AbstractTreeNode();
			tn.setName(map.get(groupName).toString() + "(" + map.get("count_id").toString() + ")");
			tn.setId(map.get(groupId).toString());
			tn.setTreenodePath(map.get(groupId).toString());
			tn.setLeaf(true);
			nodes.add(tn);

		}
		return nodes;
	}

	/**
	 * 获取配置视图，
	 * 
	 * @param code
	 * @return
	 */
	public ConfCi findOneByCode(String code) {
		return this.confCiMapper.findOneByCode(code);
	}

	public DataResponse<ConfCi> listCiByFilter(List<Map<String, String>> ps, JqGridFilter filter, String... columns) {
		String searchStr = "";

		JqGridRequest request = new JqGridRequest();
		request.isPageable(false);

		request.setFilters(JsonBuilder.buildNormalBinder().toJson(filter));

		if (ps != null && ps.size() > 0) {
			for (Map<String, String> map : ps) {
				searchStr += " id IN (SELECT conf_ci_id FROM conf_ci_property WHERE conf_category_property_id='" + map.get("pId") + "' AND value " + String.format(JqGridFilterRuleOp.valueOf(map.get("pOp")).toString(), map.get("pValue")) + ")";

			}
			request.setSearchString(searchStr);
		}
		return this.confCiMapper.findAllByRequest(request);
	}

	/**
	 * 
	 * @param ps
	 * @param request
	 * @return
	 */
	
	
	public DataResponse<Map<String, Object>> listCiMapForPortalByRequest(JqGridRequest request) {
		return this.confCiMapper.findCiMapForPortalByRequest(request);
	}
	

	public DataResponse<Map<String, Object>> listCiMapByRequest(List<Map<String, String>> ps, JqGridRequest request) {
		String leftSql = "";
		if (ps != null && ps.size() > 0) {
			for (Map<String, String> map : ps) {

				// request.addSelectColumns(map.get("pColumn") + "t."
				// + map.get("pColumn"));

				int lenCloumn = request.getSelectColumns().length - 1;
				PCOLUMN: for (int i = lenCloumn; i > -1; i--) {
					String s = request.getSelectColumns()[i];
					if (s.equals(map.get("pColumn"))) {
						request.getSelectColumns()[i] = map.get("pColumn") + "t." + map.get("pColumn");

						break PCOLUMN;
					}
				}

				leftSql += " LEFT JOIN(" + "SELECT  conf_ci_id, " + "  VALUE AS " + map.get("pColumn") + "  FROM v_conf_property  WHERE  NAME = '" + map.get("pId") + "'  GROUP BY conf_ci_id" + ")" + map.get("pColumn") + "t ON a.id =" + map.get("pColumn") + "t.conf_ci_id ";

				request.addFilterRule(map.get("pColumn") + "t." + map.get("pColumn"), JqGridFilterRuleOp.valueOf(map.get("pOp")), map.get("pValue"));
			}

			// request.setGroupBy(request.getSelectColumnsString());

			request.setJoinString(leftSql);
			return this.confCiMapper.findAllDetailByRequestWithProperty(request);

		} else {
			return this.confCiMapper.findCiMapByRequest(request);
		}

	}

	public List<ConfCiDTO> findByProperty(String propertyName, String propertyValue, String... selectColumns) {

		JqGridRequest request = new JqGridRequest();
		request.setPageable(false);
		request.setSelectColumns(selectColumns);
		request.addFilterRule(new JqGridFilterRule("propertyName", JqGridFilterRuleOp.eq, propertyName));
		request.addFilterRule(new JqGridFilterRule("propertyValue", JqGridFilterRuleOp.eq, propertyValue));
		return this.confCiMapper.findByProperty(request);

	}

	public List<Map<String, Object>> findCiPropertyPlanChange(String changeId) {

		return this.chgChangeService.findCiPropertyPlanChange(changeId);
	}

	public List<String> findCiIdsByProperties(Map<String, String> properties) {

		String searchStr = " SELECT id FROM conf_ci  WHERE status='" + CI_STATUS_USE + "' ";

		for (String key : properties.keySet()) {
			searchStr += " AND id IN( SELECT conf_ci_id FROM conf_ci_property WHERE  conf_category_property_id='" + key + "' AND value='" + properties.get(key) + "')";

		}
		return jdbcTemplate.queryForList(searchStr, String.class);
	}

	public void saveAuditCi(String ids, String auditResult, String auditDate) {

		String[] tids = ids.split(",");
		String t = "";
		for (int i = 0; i < tids.length; i++) {
			t += "'" + tids[i] + "',";
		}
		ids = t.substring(0, t.length() - 1);

		String sql = "update conf_ci set audit_result='" + auditResult + "',audit_date='" + auditDate + "' where id in(" + ids + ")";
		jdbcTemplate.update(sql);
	}

	public List<ConfCi> findByCategory(String categoryId, String... selectColumns) {

		JqGridRequest request = new JqGridRequest();
		request.setPageable(false);
		request.setSelectColumns(selectColumns);

		request.addFilterRule(new JqGridFilterRule("confCategoryId", JqGridFilterRuleOp.eq, categoryId));
		request.addFilterRule(new JqGridFilterRule("status", JqGridFilterRuleOp.eq, CI_STATUS_USE));

		return this.confCiMapper.findAllByRequest(request);

	}

	/**
	 * 批量更新资产
	 * 
	 * @param ci
	 * @param req
	 */
	public void batchUpdate(ConfCi ci, JqGridRequest req) {
		// 根据条件获取到
		List<ConfCi> cis = this.findAll(req);
		for (ConfCi e : cis) {
			BeanUtils.copyNotEmptyProperties(ci, e);
			this.saveOrUpdate(e);
		}
	}

	public List<Map<String, Object>> findCiInspect(String ciId) {
		return chgChangeService.findCiInspectDetail(ciId);
	}

	public List<Map<String, Object>> findCiUseChgColumns() {
		String sql = "select A.name,a.column_name,A.ORDER_NUM,B.control_type,B.content_value,ifnull(C.NAME,'') AS filed_category_name from db_entity_field A left join db_entity_field_control B on A.ID=B.DB_ENTITY_FIELD_ID left join APP_DICT C ON C.CODE = A .filed_category_name AND C.TYPE_CODE = 'CHANGE_CIFILEDCATEGORY' where   A.USE_FOR_CHANGE=1 AND A.DB_ENTITY_ID='9a9028d4-0b82-1031-9aa9-d1aff7ca10ee' ORDER BY C.ORDER_NUM,A.ORDER_NUM";
		return jdbcTemplate.queryForList(sql);
	}

	public List<Map<String, Object>> findCiUseChgColumns(String tecServiceId, String categoryId) {
		String sql = "select A.name,a.column_name,A.ORDER_NUM,B.control_type,B.content_value,ifnull(C1.NAME,'') AS filed_category_name from db_entity_field A left join db_entity_field_control B on A.ID=B.DB_ENTITY_FIELD_ID left join APP_DICT C1 ON C1.CODE = A .filed_category_name AND C1.TYPE_CODE = 'CHANGE_CIFILEDCATEGORY' where  A.USE_FOR_CHANGE=1 AND A.DB_ENTITY_ID='9a9028d4-0b82-1031-9aa9-d1aff7ca10ee'"
				+ " and A.column_name in (select column_name columnkey from TEC_SERVICE_RELATE_CONF a left join tec_service_relate_ci_category k on k.id=a.tec_relate_ci_category_id " + " left join DB_ENTITY_FIELD c on c.id=a.conf_ci_property_id " + " where a.TEC_SERVICE_ID in (" + tecServiceId + ") AND ( (SELECT c.treenode_path FROM conf_category c WHERE c.id='" + categoryId + "') LIKE CONCAT(CONCAT('%',k.ci_category_id),'%')) and a.type='基本')" + "ORDER BY C1.ORDER_NUM,A.ORDER_NUM";
		return jdbcTemplate.queryForList(sql);
	}

	public DataResponse<Map<String, Object>> findTecServiceCi(String relationId, String tecServiceId, JqGridRequest req) {
		String sql = "";
		// 获取技术服务目录下的资产分类ID

		String sql2 = "select wmsys.wm_concat(to_char(ci_category_id)) ids from tec_service_relate_ci_category where tec_service_id in(" + tecServiceId + ") group by tec_service_id";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql2);
		if (list.size() == 0) {
			req.setSearchString(" treenode_name_path not like '%文档/%' and a.id not in(select ci_id from conf_ci_use_relation where  relation_type='changeProcess' and relation_id='" + relationId + "' " + " union all" + " select ci_id from conf_ci_use_relation where relation_id  in (select id from chg_change k where k.status<>'已关闭'))");
			return this.findAllDetailByRequest(req);

		}

		String cids = list.get(0).get("ids").toString();
		String[] categoryIds = cids.split(",");
		for (int i = 0; i < categoryIds.length; i++) {
			String categoryIdTemp = categoryIds[i];
			sql += "select a.* from conf_ci a left join conf_category b on a.conf_category_id=b.id where a.id not in(select ci_id from conf_ci_use_relation where  relation_type='changeProcess' and relation_id='" + relationId + "' " + " union all" + " select ci_id from conf_ci_use_relation where relation_id  in (select id from chg_change k where k.status<>'已关闭')) b.treenode_name_path not like '%文档/' and b.treenode_path like '%" + categoryIdTemp + "%'";
			if (i != (categoryIds.length - 1))
				sql += " union all ";
		}
		sql = " FROM (" + sql + ") t where 1=1 ";
		// return this.confCiMapper.findTecServiceCi(sql,req);
		return null;
	}

	public DataResponse<ConfCi> findSparepartAll(JqGridRequest req) {

		return this.confCiMapper.findSparepartAll(req);
	}

	public DataResponse<ConfCi> findAllCompayCiByRequest(JqGridRequest req) {
		return this.confCiMapper.findAllCompayCiByRequest(req);
	}
	
	public DataResponse<Map<String, Object>> listSepAndModel(JqGridRequest request) {
			return this.confCiMapper.listSepAndModel(request);
	}
	public Map<String,Object>	getSimilarCategoryByName(String name)
	{
		String sql1="SELECT * FROM conf_category where name='"+name+"'";
		String sql2="SELECT * FROM conf_category where name LIKE '%"+name+"%'";
		
		List<Map<String,Object>> list1=jdbcTemplate.queryForList(sql1);
		if(list1.size()>0)
		{
			return list1.get(0);
		}else
		{
			List<Map<String,Object>> list2=jdbcTemplate.queryForList(sql2);
			if(list2.size()>0)
			{
				return list2.get(0);
			}else
			{
				return null;
			}
		}
	}
}
